--region [dbo].[AddUserActivity]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddUserActivity]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddUserActivity]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddUserActivity]
GO

CREATE PROCEDURE [dbo].[AddUserActivity]
	@ActivityId int,
	@UserId int,
	@UserName nvarchar(50),
	@Message nvarchar(2000),
	@Connactinfo nvarchar(100),
	@IsVerify int,
	@ApplyDate datetime,
	@Reply nvarchar(2000)
AS

INSERT INTO [UserActivity] (
	[ActivityId],
	[UserId],
	[UserName],
	[Message],
	[Connactinfo],
	[IsVerify],
	[ApplyDate],
	[Reply]
) VALUES (
	@ActivityId,
	@UserId,
	@UserName,
	@Message,
	@Connactinfo,
	@IsVerify,
	@ApplyDate,
	@Reply
)

GO
--endregion


--region [dbo].[UpdateUserActivity]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateUserActivity]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateUserActivity]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateUserActivity]
GO

CREATE PROCEDURE [dbo].[UpdateUserActivity]
	@Id int,
	@ActivityId int,
	@UserId int,
	@UserName nvarchar(50),
	@Message nvarchar(2000),
	@Connactinfo nvarchar(100),
	@IsVerify int,
	@ApplyDate datetime
AS

UPDATE [UserActivity] SET
	[ActivityId] = @ActivityId,
	[UserId] = @UserId,
	[UserName] = @UserName,
	[Message] = @Message,
	[Connactinfo] = @Connactinfo,
	[IsVerify] = @IsVerify,
	[ApplyDate] = @ApplyDate
WHERE
	[Id] = @Id

GO
--endregion

--region [dbo].[CheckUserActivity]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[CheckUserActivity]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[CheckUserActivity]') IS NOT NULL
	DROP PROCEDURE [dbo].[CheckUserActivity]
GO

CREATE PROCEDURE [dbo].[CheckUserActivity]
	@Id int,
	@IsVerify int,
	@Reply	nvarchar(2000),
	@ReplyDate datetime
AS

UPDATE [UserActivity] SET
	[IsVerify] = @IsVerify,
	[Reply] = @Reply,
	[ReplyDate] = @ReplyDate
WHERE
	[Id] = @Id

GO
--endregion

--region [dbo].[DelUserActivity]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelUserActivity]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelUserActivity]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelUserActivity]
GO

CREATE PROCEDURE [dbo].[DelUserActivity]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[UserActivity]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetUserActivity]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetUserActivity]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetUserActivity]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetUserActivity]
GO

CREATE PROCEDURE [dbo].[GetUserActivity]
	@Id int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[UserActivity].[Id],
	[ActivityId],
	'ActivityTitle'=[ActivityNews].[Title],
	[UserId],
	[UserName],
	[Message],
	[Connactinfo],
	[IsVerify],
	[ApplyDate],
	[Reply],
	[ReplyDate]
FROM
	[UserActivity], [ActivityNews]
WHERE
	[UserActivity].[ActivityId] = [ActivityNews].[Id]
	 AND [UserActivity].[Id] = @Id

GO
--endregion


--region [dbo].[GetUserActivityList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetUserActivityList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetUserActivityList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetUserActivityList]
GO

CREATE PROCEDURE [dbo].[GetUserActivityList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [UserActivity]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[UserActivity].[Id],
	[ActivityId],
	''ActivityTitle'' = [ActivityNews].[Title],
	[UserId],
	[UserName],
	[Connactinfo],
	[IsVerify],
	[ApplyDate]
FROM
	[UserActivity], [ActivityNews]
WHERE
	[UserActivity].[ActivityId] = [ActivityNews].[Id] 
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [UserActivity].[Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [UserActivity] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


